<?php
defined ( 'BASEPATH' ) or exit ( 'No direct script access allowed' );
class User_model extends CI_Model {
	public function __construct() {
		parent::__construct ();
	}
	/*
	 * 根据班级、姓名和学号返回用户的数量
	 */
	public function count_users($class,$name,$sno){
		if($class!=0){
			$this->db->where('t_user.class', $class);
		}
		if(!empty($name)){
			$this->db->like('t_user.sname', $name);
		}
		if($sno!=0){
			$this->db->where('t_user.sno', $sno);
		}
		$query = $this->db->get('t_user');
		return $query->num_rows();
	}
	/*
	 * 根据班级、姓名、学号和分页信息返回用户信息列表
	 */
	public function get_users($class,$name,$sno,$page,$offset,$order){//
		$this->db->select('t_user.id,t_user.pname,t_user.phone,t_user.sname,t_user.no,t_school.name,t_grade.name as gname,t_class.name as cname,t_user.sno');
		$this->db->from('t_user');
		$this->db->join('t_class', 't_user.class = t_class.id','left');
		$this->db->join('t_school', 't_class.schoolid = t_school.id','left');
		$this->db->join('t_grade', 't_class.gradeid = t_grade.id','left');
		if($class!=0){
			$this->db->where('t_user.class', $class);
		}
		if(!empty($name)){
			$this->db->like('t_user.sname', $name);
		}
		if($sno!=0){
			$this->db->where('t_user.sno', $sno);
		}
		$this->db->limit($page, $offset);
		if($order==0){
			$this->db->order_by("t_class.id");//按班级排序
		}else{
			$this->db->order_by("sname");//按姓名排序
		}
		
		$this->db->order_by("t_user.sno");
		$query=$this->db->get();
		return $query->result_array();
	}
	/*
	 *返回所有用户
	 */
	public function find_all_user(){
		$this->db->select('t_user.id,t_user.phone,t_user.password,t_user.sno,t_user.sname,t_user.order_state,t_school.name,t_grade.name as gname,t_class.name as cname,t_class.schoolid as school');
		$this->db->from('t_user');
		$this->db->join('t_class', 't_user.class = t_class.id','left');
		$this->db->join('t_school', 't_class.schoolid = t_school.id','left');
		$this->db->join('t_grade', 't_class.gradeid = t_grade.id','left');
		$this->db->where('test=0');
		$this->db->order_by("t_user.class");
		$query = $this->db->get();
		return $query->result_array();
	}
	/*
	 * 删除学生信息
	 */
	public function delete($id){
		$this->db->where('id', $id);
		$this->db->delete('t_user');
		return $this->db->affected_rows()>0;
	}
	/*
	 * 根据ID得到用户信息
	 */
	public function load($id){//
		$this->db->select('t_user.id,t_user.username,t_user.password,t_user.phone,t_user.pname,t_user.sname,t_user.class,t_user.sno,t_user.balance,t_user.age,t_user.gender,t_user.regist_time,t_user.no,t_user.no2,t_class.schoolid as school,t_class.gradeid as grade');
		$this->db->from('t_user');
		$this->db->join('t_class', 't_user.class = t_class.id','left');
		$this->db->where("t_user.id", $id);
		$query = $this->db->get();
		return  $query->row_array();
	}
	/*
	 * 更新用户信息
	 */
	public function update($id,$user){
		$this->db->where('id', $id);
		$this->db->update('t_user', $user);
		return $this->db->affected_rows()>0;
	}
	/*
	 *根据班级、学号和姓名返回用户
	 */
	public function find_by_info($class,$sno,$sname){
		$this->db->where("class", $class);
		$this->db->where("sno", $sno);
		$this->db->where("sname", $sname);
		$query = $this->db->get('t_user');
		return $query->row_array();
	}
	/*
	 *根据班级返回用户
	 */
	public function find_by_class($class){
		$this->db->where("test=0");
		$this->db->where("class", $class);
		$this->db->order_by("convert(sname using gbk)");//按照中文拼音排序
		$query = $this->db->get('t_user');
		return $query->result_array();
	}
	/*
	 * 根据班级删除学生信息
	 */
	public function delete_by_class($class){
		$this->db->where('class', $class);
		$this->db->delete('t_user');
		return $this->db->affected_rows()>0;
	}
	/*
	 * 添加用户信息
	 */
	public function create($user){
		$this->db->insert('t_user', $user);
		return $this->db->insert_id();
	}
	/*
	 * 根据卡号返回用户信息
	 */
	public function find_by_no($no){
		$this->db->where('no', $no);
		$this->db->or_where('no2', $no);
		$query = $this->db->get('t_user');
		return $query->row_array();
	}
	/*
	 *返回未下单的用户(用于生成默认订单)
	 */
	public function find_unorder_users(){
		$this->db->select('t_user.id,t_class.schoolid as school');
		$this->db->from('t_user');
		$this->db->join('t_class', 't_user.class = t_class.id','left');
		$this->db->where('order_state=0');
		$this->db->where('test=0');
		$query = $this->db->get();
		return $query->result_array();
	}
	/*
	 * 更新用户下单信息
	 */
	public function update_users(){
		$sql="update t_user set order_state=0 where order_state=1";
	    $this->db->query($sql);
		return $this->db->affected_rows()>0;
	}
	/*
	 *根据姓名返回用户
	 */
	public function find_by_name($name){
		$this->db->where("sname", $name);
		$query = $this->db->get('t_user');
		return $query->row_array();
	}
	/*
	 *根据学号返回用户
	 */
	public function find_by_sno($sno){
		$this->db->where("sno", $sno);
		$query = $this->db->get('t_user');
		return $query->row_array();
	}
	/*
	 *返回没有绑定卡的用户
	 */
	public function find_nocard_users(){
		$sql="select t_user.sname,t_user.sno,t_school.name,t_grade.name as gname,t_class.name as cname from t_user,t_school,t_class,t_grade where t_user.class = t_class.id and t_class.schoolid = t_school.id and t_class.gradeid = t_grade.id and (no='' or no is null) and (no2='' or no2 is null) order by t_user.class";
	    $query=$this->db->query($sql);
		return $query->result_array();
	}
	/*
	 *返回无法下单的用户
	 */
	public function find_order_users(){
		$this->db->where('order_state=1');
		$query = $this->db->get('t_user');
		return $query->result_array();
	}
	/*
	 * 根据学校返回非测试用户的数量(用于导出订单统计)
	 */
	public function count_by_school($school){
		$this->db->join('t_class', 't_user.class = t_class.id','left');
		$this->db->where('schoolid',$school);
		$this->db->where('test=0');
		$num = $this->db->count_all_results('t_user');
		return $num;
	}
	/*
	 * 根据学校和年级返回非测试用户的数量(用于导出订单统计)
	 */
	public function count_by_school_grade($school,$grade){
		$this->db->join('t_class', 't_user.class = t_class.id','left');
		$this->db->where('schoolid',$school);
		$this->db->where('gradeid',$grade);
		$this->db->where('test=0');
		$num = $this->db->count_all_results('t_user');
		return $num;
	}
	/*
	 *返回所有用户
	 */
	public function find_all_users(){
		$this->db->select('t_user.id,t_user.order_state,t_class.schoolid as school');
		$this->db->from('t_user');
		$this->db->join('t_class', 't_user.class = t_class.id','left');
		$this->db->where('test=0');
		$query = $this->db->get();
		return $query->result_array();
	}
}